Redshift Data APIにおけるトランザクションの挙動について確認してみた

八木優成

2023.03.17

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

こんにちは、データアナリティクス事業本部の八木です。
皆さん、Redshift Data API使ってますか？

Redshiftにクエリを実行する際、通常はVPC内のRedshiftエンドポイントに対してコネクションを確立し、クエリを実行しますが、Redshift Data APIはRedshiftのサービスエンドポイントを経由してクエリを実行します。
この機能により、プライベートサブネットに存在するRedshiftにVPC外からもクエリを実行できるなど、非常に便利な使い方ができます。

今回はこのRedshift Data APIでトランザクションを行った際の挙動について、確認してみました。

先に結論

BatchExecuteStatements APIでは BEGIN, COMMIT句なしでデフォルトでトランザクションが張られる
BatchExecuteStatements APIの挙動はAUTOCOMMITがオフの状態と同じ挙動になる

検証

前提環境

Redshift dc2.large x 1ノード
AWS CLI v2.11.3

事前準備

事前準備としてテーブルを作成しておきます。

CREATE TABLE users (
  id INT,
  name VARCHAR(50),
  age INT
);

BatchExecuteStatements APIで `BEGIN`, `COMMIT`句がない場合の挙動

こちらはドキュメントにも記載がある通り、単一のトランザクション内で実行されます。

BatchExecuteStatement API オペレーションの Sqls パラメータで SQL ステートメントが単一のトランザクションとして実行されます。
Amazon Redshift Data API の使用 - Amazon Redshift

以下のクエリをBatchExecuteStatements APIで実行してみます。

INSERT INTO users VALUES (1, '佐藤', 25);
INSERT INTO users VALUES (2, '鈴木', 30);
INSERT INTO users VALUES (3, '高橋', 45);
INSERT INTO users VALUES (4, '田中', 27);
INSERT INTO users VALUES (5, '渡辺', 38);

aws redshift-data batch-execute-statement \
  --cluster-identifier my-redshift \
  --database dev \
  --db-user admin \
  --sqls "[
    \"INSERT INTO users VALUES (1, '佐藤', 25);\",
    \"INSERT INTO users VALUES (2, '鈴木', 30);\",
    \"INSERT INTO users VALUES (3, '高橋', 45);\",
    \"INSERT INTO users VALUES (4, '田中', 27);\",
    \"INSERT INTO users VALUES (5, '渡辺', 38);\"
  ]"

クエリ履歴からトランザクションID（xid）を見てみると、単一トランザクション内で実行されていることが確認できました。

dev=# select userid, query, xid, starttime, trim(querytxt)  from stl_query where userid = 100 order by starttime desc limit 10;
 userid |  query  |  xid   |         starttime          |                                                          btrim
--------+---------+--------+----------------------------+--------------------------------------------------------------------------------------------------------------------------
    100 | 5000575 | 108362 | 2023-03-17 06:17:22.028129 | INSERT INTO users VALUES (5, '渡辺', 38);
    100 | 5000573 | 108362 | 2023-03-17 06:17:21.96298  | INSERT INTO users VALUES (4, '田中', 27);
    100 | 5000571 | 108362 | 2023-03-17 06:17:21.898675 | INSERT INTO users VALUES (3, '高橋', 45);
    100 | 5000569 | 108362 | 2023-03-17 06:17:21.834096 | INSERT INTO users VALUES (2, '鈴木', 30);
    100 | 5000567 | 108362 | 2023-03-17 06:17:21.768128 | INSERT INTO users VALUES (1, '佐藤', 25);
~~（略）~~

BatchExecuteStatements APIでは `BEGIN`, `COMMIT`句がある場合はどうなるのか

BEGIN, COMMIT句がない場合はドキュメントの通りでした。

ではクエリの中に明示的にBEGIN, COMMIT句を入れるとどうなるのでしょうか？BEGIN,COMMITを無視して単一トランザクションになるのでしょうか？それとも別のトランザクションが張られるのでしょうか？

試してみます！

以下のクエリをBatchExecuteStatements APIで実行してみます。

INSERT INTO users VALUES (6, '山田', 33);
INSERT INTO users VALUES (7, '中村', 50);
BEGIN;
INSERT INTO users VALUES (8, '小林', 29);
INSERT INTO users VALUES (9, '加藤', 42);
COMMIT;
INSERT INTO users VALUES (10, '吉田', 36);
INSERT INTO users VALUES (11, '八木', 24);

aws redshift-data batch-execute-statement \
  --cluster-identifier my-redshift \
  --database dev \
  --db-user admin \
  --sqls "[
    \"INSERT INTO users VALUES (6, '山田', 33);\",
    \"INSERT INTO users VALUES (7, '中村', 50);\",
    \"BEGIN;\",
    \"INSERT INTO users VALUES (8, '小林', 29);\",
    \"INSERT INTO users VALUES (9, '加藤', 42);\",
    \"COMMIT;\",
    \"INSERT INTO users VALUES (10, '吉田', 36);\",
    \"INSERT INTO users VALUES (11, '八木', 24);\"
  ]"

クエリ履歴を確認すると、以下のようなっていました。

dev=# select userid, query, xid, starttime, trim(querytxt)  from stl_query where userid = 100 order by starttime desc limit 10;
 userid |  query  |  xid   |         starttime          |                                                          btrim
--------+---------+--------+----------------------------+--------------------------------------------------------------------------------------------------------------------------
    100 | 5000786 | 109061 | 2023-03-17 06:31:20.032541 | INSERT INTO users VALUES (11, '八木', 24);
    100 | 5000784 | 109061 | 2023-03-17 06:31:19.943083 | INSERT INTO users VALUES (10, '吉田', 36);
    100 | 5000782 | 109060 | 2023-03-17 06:31:19.773401 | INSERT INTO users VALUES (9, '加藤', 42);
    100 | 5000780 | 109060 | 2023-03-17 06:31:19.711847 | INSERT INTO users VALUES (8, '小林', 29);
    100 | 5000778 | 109060 | 2023-03-17 06:31:19.563472 | INSERT INTO users VALUES (7, '中村', 50);
    100 | 5000776 | 109060 | 2023-03-17 06:31:19.502955 | INSERT INTO users VALUES (6, '山田', 33);

COMMIT句の部分で一度コミットされ、その後別トランザクションが始まっています。
そう、AUTOCOMMITがオフの状態と同じ挙動になっています。

試しにpsqlコマンドでAUTOCOMMITをオフにして、同じクエリを実行してみましょう。

dev=# \set AUTOCOMMIT off
dev=# INSERT INTO users VALUES (6, '山田', 33);
INSERT 0 1
dev=*# INSERT INTO users VALUES (7, '中村', 50);
INSERT 0 1
dev=*# BEGIN;
BEGIN
dev=*# INSERT INTO users VALUES (8, '小林', 29);
INSERT 0 1
dev=*# INSERT INTO users VALUES (9, '加藤', 42);
INSERT 0 1
dev=*# COMMIT;
COMMIT
dev=# INSERT INTO users VALUES (10, '吉田', 36);
INSERT 0 1
dev=*# INSERT INTO users VALUES (11, '八木', 24);
INSERT 0 1
dev=*# COMMIT;
COMMIT
dev=# select userid, query, xid, starttime, trim(querytxt)  from stl_query where userid = 100 order by starttime desc limit 10;
 userid |  query  |  xid   |         starttime          |                                                          btrim
--------+---------+--------+----------------------------+--------------------------------------------------------------------------------------------------------------------------
    100 | 5000962 | 109668 | 2023-03-17 06:43:28.068416 | INSERT INTO users VALUES (11, '八木', 24);
    100 | 5000958 | 109668 | 2023-03-17 06:43:22.663047 | INSERT INTO users VALUES (10, '吉田', 36);
    100 | 5000956 | 109650 | 2023-03-17 06:43:12.814659 | INSERT INTO users VALUES (9, '加藤', 42);
    100 | 5000954 | 109650 | 2023-03-17 06:43:07.581172 | INSERT INTO users VALUES (8, '小林', 29);
    100 | 5000949 | 109650 | 2023-03-17 06:42:53.03408  | INSERT INTO users VALUES (7, '中村', 50);
    100 | 5000947 | 109650 | 2023-03-17 06:42:45.553056 | INSERT INTO users VALUES (6, '山田', 33);

同じ結果となりました。Data APIからの実行と同様にCOMMITの部分で一度トランザクションが終了し、新たなトランザクションが開始されています。

最初のCOMMITのあとにエラーが発生するクエリを実行しても、COMMIT部分までは反映されていることが確認できます。

INSERT INTO users VALUES (6, '山田', 33);
INSERT INTO users VALUES (7, '中村', 50);
BEGIN;
INSERT INTO users VALUES (8, '小林', 29);
INSERT INTO users VALUES (9, '加藤', 42);
COMMIT;
INSERT INTO users VALUES (10, '吉田', 36);
INSERT INTO users VALUES ('This is invalid query');

aws redshift-data batch-execute-statement \
  --cluster-identifier my-redshift \
  --database dev \
  --db-user admin \
  --sqls "[
    \"INSERT INTO users VALUES (6, '山田', 33);\",
    \"INSERT INTO users VALUES (7, '中村', 50);\",
    \"BEGIN;\",
    \"INSERT INTO users VALUES (8, '小林', 29);\",
    \"INSERT INTO users VALUES (9, '加藤', 42);\",
    \"COMMIT;\",
    \"INSERT INTO users VALUES (10, '吉田', 36);\",
    \"INSERT INTO users VALUES ('This is invalid query');\"
  ]"

結果

dev=# select userid, query, xid, starttime, trim(querytxt)  from stl_query where userid = 100 order by starttime desc limit 10;
 userid |  query  |  xid   |         starttime          |                                                          btrim
--------+---------+--------+----------------------------+--------------------------------------------------------------------------------------------------------------------------
    100 | 5001062 | 110040 | 2023-03-17 06:50:50.846407 | Undoing 1 transactions on table 308887 with current xid 110040 : 110040
    100 | 5001061 | 110040 | 2023-03-17 06:50:50.750476 | INSERT INTO users VALUES (10, '吉田', 36);
    100 | 5001059 | 110039 | 2023-03-17 06:50:50.537952 | INSERT INTO users VALUES (9, '加藤', 42);
    100 | 5001057 | 110039 | 2023-03-17 06:50:50.439077 | INSERT INTO users VALUES (8, '小林', 29);
    100 | 5001055 | 110039 | 2023-03-17 06:50:50.21342  | INSERT INTO users VALUES (7, '中村', 50);
    100 | 5001053 | 110039 | 2023-03-17 06:50:50.11737  | INSERT INTO users VALUES (6, '山田', 33);
~~（略）~~
dev=# select * from users;
 id | name | age
----+------+-----
  6 | 山田 |  33
  7 | 中村 |  50
  8 | 小林 |  29
  9 | 加藤 |  42
(4 rows)